USE [DSRSystem]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetReferenceUniversesByNames]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spGetReferenceUniversesByNames]
GO

CREATE PROCEDURE [dbo].[spGetReferenceUniversesByNames]
	@Names NVARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @XML XML
	SET @XML = CONVERT(XML, @Names, 1)

	DECLARE @SelectedReferenceUniverses TABLE
	(
		Name nvarchar(max)
	)
	INSERT INTO @SelectedReferenceUniverses
	SELECT TN.name.value('.','nvarchar(max)') AS Name
	FROM @XML.nodes('/ReferenceUniverses') AS T(ru) outer apply T.ru.nodes('Name') as TN(name)
	

	SELECT DISTINCT
		StandardUniverses.StandardUniverseId AS ReferenceUniverseId,
		StandardUniverses.Name AS ReferenceUniverseName,
		Companies.CompanyId,
		Companies.Name AS CompanyName,
		Companies.CIQID AS CapitalIQID
	FROM [dbo].Companies AS Companies
	INNER JOIN [dbo].CompaniesInStandardUniverses AS CompaniesInStandardUniverses ON Companies.CompanyId = CompaniesInStandardUniverses.CompanyId 
	INNER JOIN [dbo].StandardUniverses AS StandardUniverses ON CompaniesInStandardUniverses.StandardUniverseId = StandardUniverses.StandardUniverseId 
	INNER JOIN @SelectedReferenceUniverses AS SelectedReferenceUniverses ON SelectedReferenceUniverses.Name = StandardUniverses.Name
	
	WHERE (StandardUniverses.StandardUniverseId <> '00000000-0000-0000-0000-000000000000')
	
	ORDER BY StandardUniverses.Name
END


GO


